<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class StatisticSmsRecord extends Model
{
    use SoftDeletes;

    protected $table = 'statistic_sms_records';

    protected $fillable = ['sms_count', 'sms_number', 'type', 'any_id'];

    //统计
    public function count()
    {
        //获取昨天0点
        $yesterday_zero = Carbon::yesterday();

        //获取今天0点
        $today_zero = Carbon::yesterday()->addHour(24);

        //单人统计
        $single_static = $this->countSingleUserSms($yesterday_zero, $today_zero);

        //整体统计
        $all_static = $this->countAllUserSms($yesterday_zero, $today_zero);

        //分支统计
        $branch_static = $this->countSectionAndBranchSms($yesterday_zero, $today_zero, 3);

        //部门统计
        $section_static = $this->countSectionAndBranchSms($yesterday_zero, $today_zero, 2);

        if (count($single_static)) {
            $this->createStaticSmsRecord($single_static, 0);
        }

        if (count($all_static)) {
            $this->createStaticSmsRecord($all_static, 1);
        }

        if (count($section_static)) {
            $this->createStaticSmsRecord($section_static, 2);
        }

        if (count($branch_static)) {
            $this->createStaticSmsRecord($branch_static, 3);
        }

        return true;
    }

    //统计单人短信
    public function countSingleUserSms($yesterday_zero, $today_zero)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy

        $distinct_users = DB::table('sms_records')->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->groupBy('carry_user_id')
            ->get(['carry_user_id']);

        if (empty($distinct_users)) {
            return [];
        }

        $now = Carbon::now()->toDateTimeString();
        $distinct_users_arr = [];
        foreach ($distinct_users->toArray() as $key => $user) {
            //统计单人发送短信的人数
            $sms_record_number = DB::table('sms_records')
                ->whereBetween('created_at', [$yesterday_zero, $today_zero])
                ->where('carry_user_id', $user->carry_user_id)
                ->groupBy('fs_user_id')
                ->get();
            $sms_number = count($sms_record_number);

            //统计单人短信发送次数
            $sms_record_count = DB::table('sms_records')
                ->whereBetween('created_at', [$yesterday_zero, $today_zero])
                ->where('carry_user_id', $user->carry_user_id)
                ->get();
            $sms_count = count($sms_record_count);

            $distinct_users_arr[$key]['sms_count'] = $sms_count;
            $distinct_users_arr[$key]['sms_number'] = $sms_number;
            $distinct_users_arr[$key]['type'] = 0;
            $distinct_users_arr[$key]['any_id'] = $user->carry_user_id;
            $distinct_users_arr[$key]['created_at'] = $now;
            $distinct_users_arr[$key]['updated_at'] = $now;
        }

        return $distinct_users_arr;
    }

    //统计整体短信
    public function countAllUserSms($yesterday_zero, $today_zero)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy

        //统计昨天整体一整天的发送次数
        $sms_count = DB::table('sms_records')
            ->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->count();

        //获取昨天整体一整天的发送集合
        $sms_number_record = DB::table('sms_records')
            ->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->groupBy('fs_user_id')
            ->get();

        //统计人数
        $sms_number = count($sms_number_record);

        $all_static = [];
        $all_static['sms_count'] = $sms_count;
        $all_static['sms_number'] = $sms_number;
        $all_static['type'] = 1;

        return $all_static;
    }

    public function countSectionAndBranchSms($yesterday_zero, $today_zero, $type)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy
        if ($type == 3) {
            //统计昨天各个分支一整天的短信发送次数集合
            $sms_count_record = DB::table('users')
                ->select(DB::raw('count(users.branch_id) as sms_count, users.branch_id'))
                ->leftJoin('sms_records as records', 'users.id', '=', 'records.carry_user_id')
                ->whereBetween('records.created_at', [$yesterday_zero, $today_zero])
                ->whereNotNull('users.branch_id')
                ->groupBy('users.branch_id')
                ->get();

            //统计昨天各个分支一整天的短信发送人数集合
            $sms_number_record = DB::table('users')
                ->select(DB::raw('count(distinct(records.fs_user_id)) as sms_number, users.branch_id'))
                ->leftJoin('sms_records as records', 'users.id', '=', 'records.carry_user_id')
                ->whereBetween('records.created_at', [$yesterday_zero, $today_zero])
                ->whereNotNull('users.branch_id')
                ->groupBy('users.branch_id')
                ->get();
        }
        if ($type == 2) {
            //统计昨天各个部门一整天的短信发送次数集合
            $sms_count_record = DB::table('users')
                ->select(DB::raw('count(users.section_id) as sms_count, users.section_id'))
                ->leftJoin('sms_records as records', 'users.id', '=', 'records.carry_user_id')
                ->whereBetween('records.created_at', [$yesterday_zero, $today_zero])
                ->whereNotNull('users.section_id')
                ->groupBy('users.section_id')
                ->get();

            //统计昨天各个部门一整天的短信发送人数集合
            $sms_number_record = DB::table('users')
                ->select(DB::raw('count(distinct(records.fs_user_id)) as sms_number, users.section_id'))
                ->leftJoin('sms_records as records', 'users.id', '=', 'records.carry_user_id')
                ->whereBetween('records.created_at', [$yesterday_zero, $today_zero])
                ->whereNotNull('users.section_id')
                ->groupBy('users.section_id')
                ->get();
        }

        $sms_record = [];
        if (empty($sms_count_record) || empty($sms_number_record)) {
            return $sms_record;
        }

        //合并数组
        $tempArr = [];
        foreach ($sms_count_record as $item) {
            if ($type == 3) {
                $tempArr[$item->branch_id] = $item->sms_count;
            }
            if ($type == 2) {
                $tempArr[$item->section_id] = $item->sms_count;
            }
        }

        $now = Carbon::now()->toDateTimeString();
        foreach ($sms_number_record as $key => $number) {
            if ($type == 3) {
                if (array_key_exists($number->branch_id, $tempArr)) {
                    $sms_record[$key]['sms_count'] = $tempArr[$number->branch_id];
                    $sms_record[$key]['sms_number'] = $number->sms_number;
                    $sms_record[$key]['type'] = $type;
                    $sms_record[$key]['any_id'] = $number->branch_id;
                    $sms_record[$key]['created_at'] = $now;
                    $sms_record[$key]['updated_at'] = $now;
                }
            }
            if ($type == 2) {
                if (array_key_exists($number->section_id, $tempArr)) {
                    $sms_record[$key]['sms_count'] = $tempArr[$number->section_id];
                    $sms_record[$key]['sms_number'] = $number->sms_number;
                    $sms_record[$key]['type'] = $type;
                    $sms_record[$key]['any_id'] = $number->section_id;
                    $sms_record[$key]['created_at'] = $now;
                    $sms_record[$key]['updated_at'] = $now;
                }
            }
        }

        return $sms_record;
    }

    public function createStaticSmsRecord($static, $type)
    {
        //0、单个客服 1、整体客服 2、部门客服 3、分支客服
        if ($type == 1) {
            self::create($static);
        } else {
            DB::table($this->table)->insert($static);
        }

        return true;
    }
}
